﻿----Es preferible hacer uso de la función, que cambiar toda la tabla.
--ALTER TABLE tbroute ALTER COLUMN r_hourexit SET DATA TYPE character varying;

--INSERTS
select adddriver(15985155,'Antonio', 'Primario');
select adddriver(30274306,'Maria', 'Reserva');
select addvehicles(92, 1001 , 'SFS-627', 9500, 15985155, 30274306, 'BUS');
select adduser('lorena', 'lorena@gmail.com', 'Lorena', 'lorena', '314814', 'lorena', 0);

--INSERT INTO tbstate VALUES('CALDAS');
INSERT INTO tbstate VALUES('CUNDINAMARCA');
INSERT INTO tbstate VALUES('RISARALDA');
INSERT INTO tbstate VALUES('ANTIOQUIA');
INSERT INTO tbstate VALUES('VALLEDELCAUCA');


SELECT addcity('BOGOTA', 'CUNDINAMARCA');
SELECT addcity('MANIZALES', 'CALDAS');
SELECT addcity('PEREIRA', 'RISARALDA');
SELECT addcity('MEDELLIN', 'ANTIOQUIA');
SELECT addcity('CALI', 'VALLEDELCAUCA');

--FUNCTIONS

-- Return a list with all data about table tbcity
-- DROP FUNCTION seekallcities();
-- SELECT seekallcities();
CREATE OR REPLACE FUNCTION seekallcities()
  RETURNS SETOF tbcity AS
$BODY$
BEGIN
  RETURN QUERY
    SELECT *
    FROM tbcity;
END;
$BODY$
  LANGUAGE plpgsql;

-- Return a list with all cities destination about the param city source
--DROP FUNCTION getcitydestination(character varying);
--SELECT getcitydestination('MANIZALES');
CREATE OR REPLACE FUNCTION getcitydestination(character varying)
  RETURNS SETOF cityroute AS
$BODY$
BEGIN
  RETURN QUERY
SELECT DISTINCT ON (c1.city_name) c1.city_name,c1.city_id
    FROM tbcity c1, tbcity c2, tbroute r
    WHERE c1.city_id = r.r_destination
    AND r.r_source = c2.city_id
    AND c2.city_name = $1;
END;
$BODY$
  LANGUAGE plpgsql;

-- DROPT TYPE dataroute
CREATE TYPE dataroute AS(  
  id integer,
  hourexit character varying,
  val integer
);
  
-- Return a list with all routes that the route city 1 and city 2 have programed
-- DROP FUNCTION gethours(character varying, character varying);
-- SELECT gethours('MANIZALES','PEREIRA');
CREATE OR REPLACE FUNCTION gethours(character varying, character varying)
  RETURNS SETOF dataroute AS
$BODY$
BEGIN
  RETURN QUERY  
	SELECT t.r_id, t.r_hourexit, t.r_cost
	FROM tbroute t, tbcity c1, tbcity c2
	WHERE c1.city_name = $1
	AND c2.city_name = $2
	AND c2.city_id = t.r_destination
	AND c1.city_id = r_source;

	--SELECT count(r.r_id)
	--FROM tbtickets t, tbroute r
	--WHERE t.t_idroute = r.r_id
	
END;
$BODY$
  LANGUAGE plpgsql;

-- Add ticket with state sold and return 0 if the insert was sucess or -3 if it was fail  
--DROP FUNCTION addticketsold(integer,character varying, integer,character varying);
--SELECT addticketsold('13','2010/12/31','12','yeimy');
CREATE OR REPLACE FUNCTION addticketsold(integer,character varying, integer,character varying)
  RETURNS integer AS
$BODY$
BEGIN
	INSERT INTO tbtickets VALUES (nextval('sequenceidticket'),'Vendido',$1,to_date($2,'MM DD YYYY'),$3,$4);
		RETURN 0;

	EXCEPTION WHEN unique_violation THEN
		RETURN -3;	

END;
$BODY$
  LANGUAGE plpgsql;

--DROP FUNCTION updateticket(integer, character varying, integer)
--SELECT updateticket('3','2010-12-08','1006')
CREATE OR REPLACE FUNCTION updateticket(integer, character varying, integer)
  RETURNS integer AS
$BODY$
BEGIN
UPDATE tbtickets SET 	t_type = 'Vendido', 
			t_position = $1, 
			t_date= to_date($2,'YYYY MM DD ')
		WHERE 	t_id = $3;
		RETURN 2;
END;
$BODY$
  LANGUAGE plpgsql;
  
  


--DROP TYPE infoticket
CREATE TYPE infoticket AS(  
  username character varying,
  citysource character varying,
  citydestination character varying,
  hourexit character varying,
  id integer,
  tdate character varying,
  ttype character varying
);


--DROP FUNCTION getinfoticket(integer)
--SELECT * FROM getinfoticket('1026')

CREATE OR REPLACE FUNCTION getinfoticket(integer)
  RETURNS SETOF infoticket AS
$BODY$
BEGIN
  RETURN QUERY
SELECT t.t_uname, c1.city_name, c2.city_name,r.r_hourexit , t.t_position, t.t_date::character varying, t.t_type
    FROM tbcity c1, tbcity c2, tbroute r, tbtickets t
    WHERE t.t_id = $1
    AND r.r_id = t.t_idroute
    AND c1.city_id = r.r_source
    AND c2.city_id = r.r_destination;
END;
$BODY$
  LANGUAGE plpgsql;


--SELECT * FROM deleteticket('1007')  
--DROP FUNCTION deleteticket(integer)
CREATE OR REPLACE FUNCTION deleteticket(integer)
  RETURNS integer AS
$BODY$
BEGIN
	DELETE 
	FROM tbtickets t
	WHERE t.t_id = $1;
	RETURN 0;
	
END;
$BODY$
  LANGUAGE plpgsql;    
  
-- Para empezar a validar los puestos ocupados  
CREATE FUNCTION getticketsroute(integer) 
RETURNS SETOF tbtickets AS
$BODY$
BEGIN
  RETURN QUERY
SELECT * FROM tbtickets t
WHERE t.t_idroute = $1;
END;
$BODY$ LANGUAGE plpgsql;  


-- DROP FUNCTION addticketreserved2(integer, character varying, integer, character varying)
--SELECT addticketreserved2('80','2010/12/31','12','yeimy');
CREATE OR REPLACE FUNCTION addticketreserved2(integer, character varying, integer, character varying)
  RETURNS integer AS
$BODY$
DECLARE 
  ticketID INTEGER;
BEGIN
	ticketID  := nextval('sequenceidticket');
	INSERT INTO tbtickets VALUES (ticketID  ,'Reservado',$1,to_date($2,'MM DD YYYY'),$3,$4);
		RETURN ticketID ;

	EXCEPTION WHEN unique_violation THEN
		RETURN -3;	

END;
$BODY$
  LANGUAGE plpgsql;


--select getticketsroute('11') 
--Muestra todos los registros asociados a una ruta
CREATE FUNCTION getticketsroute(integer) 
RETURNS SETOF tbtickets AS
$BODY$
BEGIN
  RETURN QUERY
SELECT * FROM tbtickets t
WHERE t.t_idroute = $1;
END;
$BODY$ LANGUAGE plpgsql;
